package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.obj.AidaResource;
import ro.gateway.aida.srv.IIDGenerator;



/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: AIDAResourceDB.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class AIDAResourceDB extends DBPersistenceManager
{


      private AIDAResourceDB( PersistenceToken token )
      {
            super( token );
      }

      public static AIDAResourceDB getManager( PersistenceToken token )
      {
            return new AIDAResourceDB( token );
      }

      /**
       * @param idGenerators
       * @param item
       * @throws SQLException
       */
      public void insert(
            IIDGenerator idGenerators,
            AidaResource item )
            throws SQLException
      {

            AIDAActivityObjectDB.getManager( token ).insert( idGenerators, item );
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DOC_INSERT );
                  ps.setLong( 1, item.getId() );
                  ps.setString( 2, item.getLink() );
                  ps.executeUpdate();
                  ps.close();

                  insertDescriptions( con, item );
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            return;
      }

      private void insertDescriptions(
            Connection con,
            AidaResource item )
            throws SQLException
      {

            String[] langs = item.getAvailableLanguages();
            if ( ( langs == null ) || ( langs.length < 1 ) )
            {
                  return;
            }

            try
            {
                  PreparedStatement ps = con.prepareStatement( DESC_INSERT );

                  for ( int i = 0; i < langs.length; i++ )
                  {
                        ps.clearParameters();
                        ps.setLong( 1, item.getId() );
                        ps.setString( 2, langs[ i ] );
                        ps.setString( 3, item.getDesc( langs[ i ] ) );
                        ps.setString( 4, item.getTitle( langs[ i ] ) );
                        ps.executeUpdate();
                  }

                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }

            return;
      }

      public AidaResource[] getForActivity(
            long activity_id )
            throws SQLException
      {

            Connection con = getConnection();
            AidaResource[] result = null;
            ArrayList items = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_RES_FOR_ACTIVITY );
                  ps.setLong( 1, activity_id );
                  ResultSet rs = ps.executeQuery();
                  while ( rs.next() )
                  {
                        AidaResource item = new AidaResource();
                        item.setId( rs.getLong( 1 ) );
                        item.setLink( rs.getString( 2 ) );

                        items.add( item );
                  }
                  rs.close();
                  ps.close();

                  if ( items.size() > 0 )
                  {
                        result = new AidaResource[ items.size() ];
                        items.toArray( result );
                  }
                  else
                  {
                        return null;
                  }

                  ps = con.prepareStatement( GET_DOCS_FOR_DOC );
                  for ( int i = 0; i < result.length; i++ )
                  {
                        ps.setLong( 1, result[ i ].getId() );
                        rs = ps.executeQuery();
                        while ( rs.next() )
                        {
                              String lang = rs.getString( 1 );
                              result[ i ].setDesc( lang, rs.getString( 2 ) );
                              if ( rs.wasNull() )
                              {
                                    result[ i ].setDesc( lang, null );
                              }
                              result[ i ].setTitle( lang, rs.getString( 3 ) );
                              if ( rs.wasNull() )
                              {
                                    result[ i ].setTitle( lang, null );
                              }
                        }
                        rs.close();
                  }
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            return result;
      }

      private void delete_descs(
            long id )
            throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_DESCS );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }

      private void delete_ress(
            long id )
            throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_RES );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }


      private void delete_descs_for_activity(
            long id )
            throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_DESCS_for_activity );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }

      private void delete_ress_for_activity(
            long id )
            throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_RES_for_activity );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }

      public void delete(
            long id )
            throws SQLException
      {
            delete_descs( id );
            delete_ress( id );
      }

      public void delete_for_activity( long id )
            throws SQLException
      {
            delete_descs_for_activity( id );
            delete_ress_for_activity( id );
            AIDAActivityObjectDB.getManager( token ).deleteForActivityByType(
                  id, AIDAActivityObject.TYPE_RESOURCE );
            AIDAActivityObjectDB.getManager( token ).deleteForActivityByType(
                  id, AIDAActivityObject.TYPE_DESCRIPTION );
      }

      private static final String DOC_INSERT =
            "INSERT INTO res_ress (id, link) " +
            "VALUES (?,?)";
      private static final String DESC_INSERT =
            "INSERT INTO res_descs (id, lang, description, title) " +
            "VALUES (?, ?, ?,?)";
      private static final String GET_RES_FOR_ACTIVITY =
            "SELECT r.id, r.link " +
            "FROM res_ress r, objects o " +
            "WHERE o.activity_id=? AND o.id=r.id";
      private static final String GET_DOCS_FOR_DOC =
            "SELECT lang, description, title " +
            "FROM res_descs WHERE id=?";

      private static final String DELETE_DESCS =
            "DELETE FROM res_descs " +
            "WHERE id = ?";
      private static final String DELETE_RES =
            "DELETE FROM res_ress " +
            "WHERE id = ?";
      private static final String DELETE_DESCS_for_activity =
            "DELETE FROM res_descs " +
            "WHERE id IN (SELECT id FROM objects WHERE activity_id=? AND obj_type=14)";
      private static final String DELETE_RES_for_activity =
            "DELETE FROM res_ress " +
            "WHERE id IN (SELECT id FROM objects WHERE activity_id=? AND obj_type=22)";
}
